package yicr.untils;


import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefPtgBase;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import yicr.model.excel.CellTypeAndValue;

import java.io.*;
import java.util.Collection;
import java.util.Date;

public class NslUtilExcelFormula {


    /**
     * 刷新单元格公式
     * @param sheet
     */
    public static void calculateFormulaOfSheet(Sheet sheet){
        Workbook workbook=sheet.getWorkbook();
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        for (Row r : sheet) {
            int rowNumber=r.getRowNum();
            for (Cell c : r) {
                int columnNumber=c.getColumnIndex();
                if (c.getCellType() == CellType.FORMULA) {
                    evaluator.evaluateFormulaCell(c);
                }
            }
        }
    }

    public static void calculateFormulaOfWorkbook(Workbook workbook){
       int size=workbook.getNumberOfSheets();
        for (int i = 0; i < size; i++) {
            Sheet sheet=workbook.getSheetAt(i);
            calculateFormulaOfSheet(sheet);
        }
    }

    public static void calculateFormulaOfWorkbook(Workbook workbook,String sheetName){
        int size=workbook.getNumberOfSheets();
        Sheet sheet=workbook.getSheet(sheetName);
        calculateFormulaOfSheet(sheet);
    }

    public static void calculateFormulaOfWorkbook(String excelFilePath,String sheetName) throws FileNotFoundException {
        NslUtilFilePath.assertFileExistNotDir(new File(excelFilePath));

        try (InputStream inp = new FileInputStream(excelFilePath)) {
//InputStream inp = new FileInputStream("workbook.xlsx");
            Workbook wb = WorkbookFactory.create(inp);
            calculateFormulaOfWorkbook(wb,sheetName);
            try (OutputStream fileOut = new FileOutputStream(excelFilePath)) {
                wb.write(fileOut);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 运算 指定sheet的公式，然后 数值型粘贴
     * @param excelFilePath
     * @param sheetName
     * @throws FileNotFoundException
     */
    public static void calculateFormulaAndSaveAsValueOfWorkbook(String excelFilePath,String sheetName) throws FileNotFoundException {
        NslUtilFilePath.assertFileExistNotDir(new File(excelFilePath));
        try (InputStream inp = new FileInputStream(excelFilePath)) {
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet=wb.getSheet(sheetName);
            calculateFormulaOfSheet(sheet);
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            formula2Value(sheet,evaluator);
            try (OutputStream fileOut = new FileOutputStream(excelFilePath)) {
                wb.write(fileOut);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static void calculateFormulaOfWorkbook(String excelFilePath) throws FileNotFoundException {
        NslUtilFilePath.assertFileExistNotDir(new File(excelFilePath));
        try (InputStream inp = new FileInputStream(excelFilePath)) {
            Workbook wb = WorkbookFactory.create(inp);
            calculateFormulaOfWorkbook(wb);

            try (OutputStream fileOut = new FileOutputStream(excelFilePath)) {
                wb.write(fileOut);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * 将 excel文件中指定的sheet ，进行数值型粘贴
     * @param excelFile
     * @param sheetName
     */
    public static void formula2Value(File excelFile,String sheetName){
        try (InputStream inp = new FileInputStream(excelFile)) {
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheet(sheetName);

            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            formula2Value(sheet,evaluator);

            OutputStream fileOut = new FileOutputStream(excelFile);
            wb.write(fileOut);

        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    public static void formula2Value(String excelFilePath,String sheetName){
        formula2Value(new File(excelFilePath),sheetName);
    }

    public static void formula2Value(String excelFilePath, Collection<String> sheetNameCollection){
        try (InputStream inp = new FileInputStream(excelFilePath)) {
            Workbook wb = WorkbookFactory.create(inp);
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            sheetNameCollection.stream().forEach(sheetName->{
                Sheet sheet = wb.getSheet(sheetName);
                formula2Value(sheet,evaluator);
            });

        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static void formula2Value(Sheet sheet){
        Workbook workbook=sheet.getWorkbook();
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formula2Value(sheet,evaluator);
    }


    /**
     * 将sheet中的公式 都 赋值为相应的数值或字符串
     * @param sheet
     */
    public static void formula2Value(Sheet sheet,FormulaEvaluator evaluator){
//        evaluator.evaluateAll();
        for (Row r : sheet) {
            for (Cell c : r) {
                formulaCell2Value(c,evaluator);
            }
        }
    }

    public static void formulaCell2Value(Cell cell,FormulaEvaluator evaluator){
        if(cell==null){
            throw new RuntimeException("cell is null");
        }
        if(cell.getCellType()==CellType.FORMULA){
            Object value=calFormulaCellValueAsStringOrNumber(cell,evaluator);
            cell.removeFormula();
            if(value instanceof Double){
                cell.setCellValue(((Double) value).doubleValue());
            }else{
                cell.setCellValue(value.toString());
            }
        }
    }


    /**
     * 将cell中的公式计算值，变成 相应的数值或者字符串
     */
    public static void formula2Value(Cell cell){
        if(cell==null){
            throw new RuntimeException("cell is null");
        }
//        FormulaEvaluator evaluator =  cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

        if(cell.getCellType()==CellType.FORMULA){
//            evaluator.evaluateFormulaCell(cell);
            String cellValue="";
            boolean isNumber=false;
            try {
                cellValue = cell.getStringCellValue();
            } catch (IllegalStateException e) {
                cellValue = String.valueOf(cell.getNumericCellValue());
                isNumber=true;
            }
            cell.removeFormula();
            if(isNumber){
                cell.setCellValue(Double.parseDouble(cellValue));
            }else{
                cell.setCellValue(cellValue);
            }
        }
    }





    /**
     * 当poi 输出excel时， 需要处理公式。 并且要求公式 能像excel中复制一数，非绝对引用会自动变。比如 第一行第一列公式为：A1+B1 ，把它COPY到第二行时，应为A2+B2
     * @param sheet
     * @param org
     * @param dest
     */
    public static void copyFormula(Sheet sheet, Cell org, Cell dest) {
        if (org == null || dest == null || sheet == null
                || org.getCellType() != CellType.FORMULA)
            return;
        if (org.isPartOfArrayFormulaGroup())
            return;
        String formula = org.getCellFormula();
        int shiftRows = dest.getRowIndex() - org.getRowIndex();
        int shiftCols = dest.getColumnIndex() - org.getColumnIndex();

//        Workbook workbook=sheet.getWorkbook();
//        workbook.getCreationHelper().

//        HSSFEvaluationWorkbook workbookWrapper = HSSFEvaluationWorkbook.create((HSSFWorkbook) sheet.getWorkbook());
        XSSFEvaluationWorkbook workbookWrapper = XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());
        Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL
                , sheet.getWorkbook().getSheetIndex(sheet));
        for (Ptg ptg : ptgs) {
            if (ptg instanceof RefPtgBase) // base class for cell references
            {
                RefPtgBase ref = (RefPtgBase) ptg;
                if (ref.isColRelative())
                    ref.setColumn(ref.getColumn() + shiftCols);
                if (ref.isRowRelative())
                    ref.setRow(ref.getRow() + shiftRows);
            } else if (ptg instanceof AreaPtg) // base class for range references
            {
                AreaPtg ref = (AreaPtg) ptg;
                if (ref.isFirstColRelative())
                    ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
                if (ref.isLastColRelative())
                    ref.setLastColumn(ref.getLastColumn() + shiftCols);
                if (ref.isFirstRowRelative())
                    ref.setFirstRow(ref.getFirstRow() + shiftRows);
                if (ref.isLastRowRelative())
                    ref.setLastRow(ref.getLastRow() + shiftRows);
            }
        }
        formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
        dest.setCellFormula(formula);
    }

    //表格内容，公式计算后，实现值复制黏贴，即只保留数字和字符串形式

    /**
     * 计算 cell的公式，返回公式计算后的值：
     * 返回值只有以下几种类型：
     * double,string,boolean,string(错误字符串),null
     * @param cell
     * @param evaluator
     * @return
     */
    public static CellTypeAndValue calFormulaCellValue(Cell cell, FormulaEvaluator evaluator){
        CellType cellType=cell.getCellType();
        if(!cellType.equals(CellType.FORMULA)){
            throw new RuntimeException("cell.getCellType() 不是 公式 类型");
        }else{
            CellValue cellValue = evaluator.evaluate(cell);
            CellType resultType = cellValue.getCellType();
            // 判断计算结果的类型
            switch (resultType) {
                case NUMERIC:
//                    System.out.println("公式计算结果类型: 数字");
//                    System.out.println("值: " + cellValue.getNumberValue());
                    if (DateUtil.isCellDateFormatted(cell)) {
//                        System.out.println("单元格类型: 日期");
//                        System.out.println("值: " + cell.getDateCellValue());
                        return new CellTypeAndValue(resultType, cell.getDateCellValue());
                    } else {
//                        System.out.println("单元格类型: 数字");
//                        System.out.println("值: " + cell.getNumericCellValue());
                        return new CellTypeAndValue(resultType, Double.valueOf(cell.getNumericCellValue()));
                    }

                case STRING:
//                    System.out.println("公式计算结果类型: 字符串");
//                    System.out.println("值: " + cellValue.getStringValue());
                    return new CellTypeAndValue(resultType,cellValue.getStringValue());

                case BOOLEAN:
//                    System.out.println("公式计算结果类型: 布尔");
//                    System.out.println("值: " + cellValue.getBooleanValue());
                    return new CellTypeAndValue(resultType,cellValue.getBooleanValue());

                case ERROR:
//                    System.out.println("公式计算结果类型: 错误");
//                    System.out.println("错误代码: " + cellValue.getErrorValue());
                    return new CellTypeAndValue(resultType,cellValue.getErrorValue());
                case BLANK:
//                    System.out.println("公式计算结果类型: 空");
                    return new CellTypeAndValue(resultType,null);
                default:
//                    System.out.println("未知公式计算结果类型");
                    throw new RuntimeException("未知公式计算结果类型");
            }
        }
    }

    /**
     * 计算cell公式，以字符串或者数字形式返回
     * 日期时间 以字符串形式返回
     * 数字形式为 Double
     * @param cell
     * @param evaluator
     * @return
     */
   static public Object calFormulaCellValueAsStringOrNumber(Cell cell,FormulaEvaluator evaluator){
        CellTypeAndValue cellTypeAndValue=calFormulaCellValue(cell,evaluator);
        CellType cellType=cellTypeAndValue.getCellTypeEnum();
        Object cellValue=cellTypeAndValue.getCellValue();
        switch (cellType) {
            case STRING:
                return cellValue;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date=cell.getDateCellValue();
                    return NslUtilDateTime.formatAsYyyyMmDdHhMmSs(date);
                } else {
                    return  cellValue;
                }
            case BOOLEAN:
                return cellValue+"";
            case BLANK:
                return "";
            case ERROR:
                return NslUtilExcelCommonUtils.getErrorMessage((Byte) cellValue);
            default:
                throw new RuntimeException("cell 类型不可知");
        }

    }

    //表格内容，公式计算后，实现值复制黏贴，即只保留字符串形式
    /**
     * 计算cell公式，以字符串
     * 日期时间 以字符串形式返回
     * 数字形式为 Double
     * @param cell
     * @param evaluator
     * @return
     */
    static public Object calFormulaCellValueAsString(Cell cell,FormulaEvaluator evaluator){
       return calFormulaCellValueAsStringOrNumber(cell,evaluator).toString();
    }
}
